package cn.creditease.std.dao;

import cn.creditease.std.enums.JobStatusEnums;
import cn.creditease.std.model.Job;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

/**
 * @Description: job对应数据执行mapper
 * @Author: kai.yang
 * @Date: 2019-08-05 17:14
 */
public class JobDAO {

    private static final Logger logger = LoggerFactory.getLogger(JobDAO.class);


    private static JobDAO jobDao = new JobDAO();

    private JobDAO() {
    }

    DataSource dataSource;

    public JobDAO setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
        return this;
    }

    public static JobDAO newInstance() {
        return jobDao;
    }

    private static final String BATCH_INSERT_PRE = "insert tbl_allot_job (job_id,job_group_name,page_size,page_no,job_status,retry_count,comsumer_id,max_duration) values %s";

    private static final String BATCH_INSERT_SUF = "(?,?,?,?,?,?,?,?)";


    private static final String UPDATE_SQL_COMMAND = "update tbl_allot_job set job_status=?   ,  update_time= ? where id=? and job_status=?";

    private static final String UPDATE_SQL_COMMAND_WITH_CONSUMER = "update tbl_allot_job set job_status=?   ,  update_time= ? ,comsumer_id=? where id=? and job_status=?";


    private static final String UPDATE_SQL_COMMAND_ADD_COUNT = "update tbl_allot_job set job_status=?   , retry_count=retry_count+1 , update_time= ? where id=? and job_status=?";


    private static final String QUERY_EXPAIR_PROCESSIONG_JOB =
        "SELECT  id, job_id, job_group_name,page_size,page_no,job_status,retry_count,comsumer_id,max_duration,create_time,update_time  \n"
            + "FROM tbl_allot_job  where  job_group_name=? and update_time< date_add(now(), interval - max_duration minute)\n"
            + "\n and job_status='PROCESS' ";


    private static final String QUERY_BY_CREATETIME_AND_STATUS_NAME =
        "SELECT  id, job_id, job_group_name,page_size,page_no,job_status,retry_count,comsumer_id,max_duration,create_time,update_time "
            + "FROM "
            + "    tbl_allot_job where  %s job_group_name =?  and job_status in (%s)  order by id desc limit ?";

    private static final String QUERY_BY_CREATETIME_AND_STATUS_ID =
        "SELECT  id, job_id, job_group_name,page_size,page_no,job_status,retry_count,comsumer_id,max_duration,create_time,update_time "
            + "FROM "
            + "    tbl_allot_job where %s job_id =?  and job_status in (%s) order by id desc limit ?";


    /**
     * 查询已过期的任务
     */
    public List<Job> queryExpairJob(String jobName) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            int count = 1;
            connection = dataSource.getConnection();
            preparedStatement = connection.prepareStatement(QUERY_EXPAIR_PROCESSIONG_JOB);
            preparedStatement.setString(count++, jobName);

            ResultSet resultSet = preparedStatement.executeQuery();
            return changeToJobs(resultSet);
        } catch (Throwable e) {
            logger.info("批量新增分发任务数据异常", e);
        } finally {
            close(preparedStatement, connection);
        }

        return null;
    }

    /**
     * 批量新增job
     */
    public int insertBatch(List<Job> jobs) {

        StringBuilder values = null;

        if (!CollectionUtils.isEmpty(jobs)) {
            values = new StringBuilder(BATCH_INSERT_SUF);
            for (int i = 1; i < jobs.size(); i++) {
                values.append(",").append(BATCH_INSERT_SUF);
            }
        }
        if (values == null) {
            return 0;
        }
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            int count = 1;
            connection = dataSource.getConnection();
            preparedStatement = connection.prepareStatement(String.format(BATCH_INSERT_PRE, values));
            for (int i = 0; i < jobs.size(); i++) {
                preparedStatement.setString(count++, jobs.get(i).getJobId());
                preparedStatement.setString(count++, jobs.get(i).getJobGroupName());
                preparedStatement.setInt(count++, jobs.get(i).getPageSize());
                preparedStatement.setInt(count++, jobs.get(i).getPageNo());
                preparedStatement.setString(count++, jobs.get(i).getJobStatus().toString());
                preparedStatement.setInt(count++, jobs.get(i).getRetryCount());
                preparedStatement.setString(count++, jobs.get(i).getComsumerId());
                preparedStatement.setInt(count++, jobs.get(i).getMaxDuration());
            }

            return preparedStatement.executeUpdate();
        } catch (Throwable e) {
            logger.info("批量新增分发任务数据异常", e);
        } finally {
            close(preparedStatement, connection);
        }

        return 0;
    }


    /**
     * 更新job状态
     */
    public int updateStatus(Long id, String oldStatus, String newStatus) {
        return update(id, oldStatus, newStatus, UPDATE_SQL_COMMAND);
    }

    /**
     * 更新job状态和消费标志
     */
    public int updateStatusAndConsumerId(Long id, String consumerId, String oldStatus, String newStatus) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = dataSource.getConnection();
            int count = 1;
            preparedStatement = connection.prepareStatement(UPDATE_SQL_COMMAND_WITH_CONSUMER);
            preparedStatement.setString(count++, newStatus);
            preparedStatement.setTimestamp(count++, Timestamp.valueOf(LocalDateTime.now()));
            preparedStatement.setString(count++, consumerId);
            preparedStatement.setLong(count++, id);
            preparedStatement.setString(count++, oldStatus);
            return preparedStatement.executeUpdate();
        } catch (Throwable e) {
            logger.info("更新分发任务数据状态异常", e);
        } finally {
            close(preparedStatement, connection);
        }

        return 0;
    }

    private int update(Long id, String oldStatus, String newStatus, String sql) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = dataSource.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, newStatus);
            preparedStatement.setTimestamp(2, Timestamp.valueOf(LocalDateTime.now()));
            preparedStatement.setLong(3, id);
            preparedStatement.setString(4, oldStatus);
            return preparedStatement.executeUpdate();
        } catch (Throwable e) {
            logger.info("更新分发任务数据状态异常", e);
        } finally {
            close(preparedStatement, connection);
        }

        return 0;
    }

    /**
     * 更新job状态，并retryCount加1
     */
    public int updateStatusAndAddCount(Long jobId, String oldStatus, String newStatus) {
        return update(jobId, oldStatus, newStatus, UPDATE_SQL_COMMAND_ADD_COUNT);

    }


    /**
     * 查询job信息,限制页码
     */
    public List<Job> queryByJobNameAndStatusAndCreateTimeLimit(String jobName, Long limitStart,
        int limitEnd,
        JobStatusEnums... status) {
        return queryByIdOrName(jobName, limitStart, limitEnd, false, status);
    }

    private List<Job> queryByIdOrName(String id, Long limitStartId,
        int limit, boolean isId,
        JobStatusEnums... status) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        if (StringUtils.isEmpty(id) || status == null || status.length == 0) {
            return null;
        }

        try {
            int count = 1;
            connection = dataSource.getConnection();

            String sql = isId ? QUERY_BY_CREATETIME_AND_STATUS_ID : QUERY_BY_CREATETIME_AND_STATUS_NAME;
            String fomartInStatusParams = getFomartInParams(status == null ? 0 : status.length);
            if (limitStartId != null) {
                sql = String.format(sql, "id <" + limitStartId + " and", fomartInStatusParams);
            } else {
                sql = String.format(sql, "", fomartInStatusParams);
            }

            preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setString(count++, id);

            for (int i = 0; i < status.length; i++) {
                preparedStatement.setString(count++, status[i].name());
            }

            preparedStatement.setInt(count++, limit);

            //执行查询
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet != null) {
                return changeToJobs(resultSet);
            }

        } catch (
            Throwable e) {
            logger.info("更新分发任务数据状态异常", e);
        } finally {
            close(preparedStatement, connection);
        }

        return null;


    }

    /**
     * 查询job信息,限制页码
     */
    public List<Job> queryByJobIdAndStatusAndCreateTimeLimit(String jobId, Long limitStartId,
        int limitEnd,
        JobStatusEnums... status) {
        return queryByIdOrName(jobId, limitStartId, limitEnd, true, status);

    }


    private void close(PreparedStatement preparedStatement, Connection connection) {
        try {
            if (preparedStatement != null) {
                preparedStatement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
        }
    }


    /**
     * 数据转换
     */
    private List<Job> changeToJobs(ResultSet resultSet) {
        List<Job> jobs = new ArrayList<>();
        try {
            if (resultSet == null) {
                return null;
            }

            while (resultSet.next()) {
                jobs.add(makeJob(resultSet));
            }
        } catch (Throwable e) {
            logger.error("转换resultSet异常", e);
            return null;
        }
        return jobs;
    }

    private Job makeJob(ResultSet resultSet) throws SQLException {
        Job job = new Job();
        int count = 1;
        job.setId(resultSet.getLong(count++));
        job.setJobId(resultSet.getString(count++));
        job.setJobGroupName(resultSet.getString(count++));
        job.setPageSize(resultSet.getInt(count++));
        job.setPageNo(resultSet.getInt(count++));
        job.setJobStatus(JobStatusEnums.valueOf(resultSet.getString(count++)));
        job.setRetryCount(resultSet.getInt(count++));
        job.setComsumerId(resultSet.getString(count++));
        job.setMaxDuration(resultSet.getInt(count++));
        job.setCreateTime(resultSet.getDate(count++));
        job.setUpdateTime(resultSet.getDate(count++));
        return job;
    }

    /**
     * 对应in参数映射
     */
    private String getFomartInParams(int length) {
        StringBuilder sb;
        if (length < 1) {
            throw new RuntimeException("params count is 0");
        } else {
            sb = new StringBuilder("?");
            for (int i = 1; i < length; i++) {
                sb.append(",").append("?");
            }
        }
        return sb.toString();
    }
}
